﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Common.DBUtility
{
    internal class PageHelper
    {
        /// <summary>
        /// 获取分页Sql
        /// </summary>
        /// <param name="cmdText">传入的SQL Query</param>
        /// <param name="pageIndex">当前页面</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="orderBy">排序字段，根据“，”分隔</param>
        /// <returns></returns>
        internal static string GeneratePagingSql(string cmdText, int pageIndex, int pageSize, string orderBy)
        {
            int startRecord = pageIndex <= 0 ? 1 : (pageIndex - 1)*pageSize + 1;
            var pagingSql = new StringBuilder("WITH t AS (");
            pagingSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowNumber", orderBy)
                .AppendFormat(",* FROM ({0}) AS tb) ", cmdText)
                .AppendFormat("SELECT * FROM t WHERE RowNumber BETWEEN {0} AND {1};", startRecord, (startRecord + pageSize-1 ))
                .AppendFormat("SELECT COUNT(*) FROM ({0}) temp;", cmdText);
            return pagingSql.ToString();
        }

    }


}
